using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Text;


namespace RaidLog.Engine.DAL
{
    public class WowDatabase : IDisposable
    {
        #region Fields and Properties
        SqlConnection _SqlConnection;

        public SqlConnection SqlConnection
        {
            get { return _SqlConnection; }
            //set { _SqlConnection = value; }
        }

        public string GetName()
        {
            return new SqlConnectionStringBuilder(_SqlConnection.ConnectionString).InitialCatalog;
        }

        #endregion

        #region Constructors

        public WowDatabase(SqlConnectionStringBuilder sqlConnectionStringBuilder)
        {
            sqlConnectionStringBuilder.ApplicationName = "RaidLog.Engine.DAL.WowDatabase";
            _SqlConnection = new SqlConnection(sqlConnectionStringBuilder.ConnectionString);
        }
        
        #endregion

        public void ImportData(ImportData data, int raidID, string toonName)
        {
            if (ObtainImportLock())
            {
                try
                {
                    ClearImportData();
                    UploadRawData(data);
                    CommitImportData(raidID, toonName);
                }
                finally
                {
                    ClearImportData();
                    ReleaseImportLock();
                }
            }
            else
            {
                throw new ApplicationException();
            }
        }

        public RaidData GetAllRaids()
        {
            RaidData _RaidData = new RaidData();
            DoCommand(delegate(SqlTransaction sqlTransaction) {
                RaidDataTableAdapters.RaidTableAdapter _RaidTableAdapter = new RaidDataTableAdapters.RaidTableAdapter();
                _RaidTableAdapter.Connection = sqlTransaction.Connection;
                _RaidTableAdapter.Transaction = sqlTransaction;
                _RaidTableAdapter.Fill(_RaidData.Raid);
            });
            return _RaidData;
        }

        public LootData GetLootCredit(int raidID)
        {
            LootData _Data = new LootData();
            _Data.EnforceConstraints = false;

            DoCommand(delegate(SqlTransaction sqlTransaction)
            {
                LootDataTableAdapters.RaidTableAdapter _RaidTableAdapter = new LootDataTableAdapters.RaidTableAdapter();
                _RaidTableAdapter.Connection = sqlTransaction.Connection;
                _RaidTableAdapter.Transaction = sqlTransaction;
                _RaidTableAdapter.Fill(_Data.Raid, raidID);

                LootDataTableAdapters.LootTableAdapter _LootTableAdapter = new LootDataTableAdapters.LootTableAdapter();
                _LootTableAdapter.Connection = sqlTransaction.Connection;
                _LootTableAdapter.Transaction = sqlTransaction;
                _LootTableAdapter.Fill(_Data.Loot, raidID);

                LootDataTableAdapters.LootCreditTableAdapter _LootCreditTableAdapter = new LootDataTableAdapters.LootCreditTableAdapter();
                _LootCreditTableAdapter.Connection = sqlTransaction.Connection;
                _LootCreditTableAdapter.Transaction = sqlTransaction;
                _LootCreditTableAdapter.Fill(_Data.LootCredit, raidID);

                LootDataTableAdapters.LootChatTableAdapter _LootChatTableAdapter = new LootDataTableAdapters.LootChatTableAdapter();
                _LootChatTableAdapter.Connection = sqlTransaction.Connection;
                _LootChatTableAdapter.Transaction = sqlTransaction;
                _LootChatTableAdapter.Fill(_Data.LootChat, raidID);

                LootDataTableAdapters.KillTableAdapter _KillTableAdapter = new LootDataTableAdapters.KillTableAdapter();
                _KillTableAdapter.Connection = sqlTransaction.Connection;
                _KillTableAdapter.Transaction = sqlTransaction;
                _KillTableAdapter.Fill(_Data.Kill, raidID);

                LootDataTableAdapters.KillCreditTableAdapter _KillCreditTableAdapter = new LootDataTableAdapters.KillCreditTableAdapter();
                _KillCreditTableAdapter.Connection = sqlTransaction.Connection;
                _KillCreditTableAdapter.Transaction = sqlTransaction;
                _KillCreditTableAdapter.Fill(_Data.KillCredit, raidID);
            });
            try
            {
                _Data.EnforceConstraints = true;
            }
            catch (ConstraintException)
            {
                PrintConstraintException(_Data);
                throw;
            }
            return _Data;
        }

        public void RegenerateAnalysis(int raidID)
        {
            DoCommand(delegate(SqlTransaction sqlTransaction)
            {
                // Execute usp_GenerateCombatWindows_ins
                SqlCommand cmd = sqlTransaction.Connection.CreateCommand();
                cmd.Transaction = sqlTransaction;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.usp_GenerateCombatWindows_ins";
                cmd.Parameters.Add("@iRaidID", SqlDbType.Int);
                cmd.Parameters["@iRaidID"].Value = raidID;
                cmd.ExecuteNonQuery();

                // Execute usp_GenerateCombatWindows_ins
                cmd = sqlTransaction.Connection.CreateCommand();
                cmd.Transaction = sqlTransaction;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.usp_GenerateMeterAnalysis_ins";
                cmd.Parameters.Add("@raidID", SqlDbType.Int);
                cmd.Parameters["@raidID"].Value = raidID;
                cmd.ExecuteNonQuery();
            });
        }

        public int CreateRaid(DateTime start, DateTime end, string instance)
        {
            RaidData data = new RaidData();
            RaidData.RaidRow raidRow = data.Raid.AddRaidRow(instance, start, end);
            DoCommand(delegate(SqlTransaction sqlTransaction)
            {
                RaidDataTableAdapters.RaidTableAdapter _mtRaidTableAdapter = new RaidDataTableAdapters.RaidTableAdapter();
                _mtRaidTableAdapter.Connection = sqlTransaction.Connection;
                _mtRaidTableAdapter.Transaction = sqlTransaction;
                _mtRaidTableAdapter.Update(raidRow);
            });
            return raidRow.RaidID;
        }

        public void InitializeDatabase()
        {
            DoCommand(delegate(SqlTransaction sqlTransaction)
            {
                _DoCommand(GetScript("FullDatabase"), sqlTransaction);
                _DoCommand(GetScript("InitData"), sqlTransaction);
            });
        }

        #region Helpers

        private void PrintConstraintException(DataSet data)
        {
            if (!data.HasErrors) throw new ApplicationException();
            foreach (DataTable table in data.Tables)
            {
                if(table.HasErrors)
                {
                    foreach (DataRow row in table.GetErrors())
                    {
                        System.Diagnostics.Trace.WriteLine(String.Format(
                            "{0}: {1}",table.TableName, row.RowError));
                    }
                }
            }
        }

        private void DoCommand(DatabaseOperation operation)
        {
            DoCommand(_SqlConnection, operation);
        }

        #region Data Import

        public bool ObtainImportLock()
        {
            return true; // TODO: Implement ObtainImportLock
        }

        public void ReleaseImportLock()
        {
            // TODO: Implement ObtainImportLock
        }

        public void UploadRawData(ImportData data)
        {
            DoCommand(delegate(SqlTransaction sqlTransaction)
            {
                ImportDataTableAdapters.RawLogLineTableAdapter _RawLogLineTableAdapter =
                    new RaidLog.Engine.DAL.ImportDataTableAdapters.RawLogLineTableAdapter();
                _RawLogLineTableAdapter.Connection = sqlTransaction.Connection;
                _RawLogLineTableAdapter.Transaction = sqlTransaction;
                _RawLogLineTableAdapter.Update(data.RawLogLine);

                ImportDataTableAdapters.DamageTableAdapter _DamageTableAdapter =
                    new RaidLog.Engine.DAL.ImportDataTableAdapters.DamageTableAdapter();
                _DamageTableAdapter.Connection = sqlTransaction.Connection;
                _DamageTableAdapter.Transaction = sqlTransaction;
                _DamageTableAdapter.Update(data.Damage);

                ImportDataTableAdapters.HealingTableAdapter _HealingTableAdapter =
                    new RaidLog.Engine.DAL.ImportDataTableAdapters.HealingTableAdapter();
                _HealingTableAdapter.Connection = sqlTransaction.Connection;
                _HealingTableAdapter.Transaction = sqlTransaction;
                _HealingTableAdapter.Update(data.Healing);

                ImportDataTableAdapters.RaidInfoTableAdapter _RaidInfoTableAdapter =
                    new RaidLog.Engine.DAL.ImportDataTableAdapters.RaidInfoTableAdapter();
                _RaidInfoTableAdapter.Connection = sqlTransaction.Connection;
                _RaidInfoTableAdapter.Transaction = sqlTransaction;
                _RaidInfoTableAdapter.Update(data.RaidInfo);

                ImportDataTableAdapters.GuildInfoTableAdapter _GuildInfoTableAdapter =
                    new RaidLog.Engine.DAL.ImportDataTableAdapters.GuildInfoTableAdapter();
                _GuildInfoTableAdapter.Connection = sqlTransaction.Connection;
                _GuildInfoTableAdapter.Transaction = sqlTransaction;
                _GuildInfoTableAdapter.Update(data.GuildInfo);

                ImportDataTableAdapters.LootTableAdapter _LootTableAdapter =
                    new RaidLog.Engine.DAL.ImportDataTableAdapters.LootTableAdapter();
                _LootTableAdapter.Connection = sqlTransaction.Connection;
                _LootTableAdapter.Transaction = sqlTransaction;
                _LootTableAdapter.Update(data.Loot);

                ImportDataTableAdapters.RollTableAdapter _RollTableAdapter =
                    new RaidLog.Engine.DAL.ImportDataTableAdapters.RollTableAdapter();
                _RollTableAdapter.Connection = sqlTransaction.Connection;
                _RollTableAdapter.Transaction = sqlTransaction;
                _RollTableAdapter.Update(data.Roll);

                ImportDataTableAdapters.DeathTableAdapter _DeathTableAdapter =
                    new RaidLog.Engine.DAL.ImportDataTableAdapters.DeathTableAdapter();
                _DeathTableAdapter.Connection = sqlTransaction.Connection;
                _DeathTableAdapter.Transaction = sqlTransaction;
                _DeathTableAdapter.Update(data.Death);

                ImportDataTableAdapters.ChatTableAdapter _ChatTableAdapter =
                    new RaidLog.Engine.DAL.ImportDataTableAdapters.ChatTableAdapter();
                _ChatTableAdapter.Connection = sqlTransaction.Connection;
                _ChatTableAdapter.Transaction = sqlTransaction;
                _ChatTableAdapter.Update(data.Chat);
            });
        }

        public void ClearImportData()
        {
            DoCommand(delegate(SqlTransaction sqlTransaction)
            {
                // Execute usp_ClearImportTables_del
                SqlCommand cmd = sqlTransaction.Connection.CreateCommand();
                cmd.Transaction = sqlTransaction;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.usp_ClearImportTables_del";
                cmd.ExecuteNonQuery();
            });
        }

        public void CommitImportData(int raidID, string toonName)
        {
            DoCommand(delegate(SqlTransaction sqlTransaction)
            {
                // Execute usp_ImportData_ins
                SqlCommand cmd = sqlTransaction.Connection.CreateCommand();
                cmd.Transaction = sqlTransaction;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.usp_ImportData_ins";
                cmd.Parameters.Add("@iRaidID", SqlDbType.Int);
                cmd.Parameters["@iRaidID"].Value = raidID;
                cmd.Parameters.Add("@iPlayer", SqlDbType.VarChar, 50);
                cmd.Parameters["@iPlayer"].Value = toonName;
                cmd.ExecuteNonQuery();
            });
        }

        #endregion

        #endregion

        #region Static Helpers

        private delegate void DatabaseOperation(SqlTransaction sqlTransaction);

        private static void DoCommand(SqlConnection sqlConnection, DatabaseOperation operation)
        {
            ConnectionState originalState = sqlConnection.State;
            if (sqlConnection.State == ConnectionState.Closed)
            {
                sqlConnection.Open();
            }
            else if (sqlConnection.State != ConnectionState.Open)
            {
                throw new InvalidOperationException();
            }

            try
            {
                using (SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
                {
                    try
                    {
                        operation(sqlTransaction);
                        sqlTransaction.Commit();
                    }
                    catch
                    {
                        sqlTransaction.Rollback();
                        throw;
                    }
                }
            }
            finally
            {
                if (originalState == ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }
        }

        private static void _DoCommand(string command, SqlTransaction sqlConnection)
        {
            foreach (string subcommand in command.Split(new string[] { "\r\nGO\r\n" }, StringSplitOptions.RemoveEmptyEntries))
            {
                if (subcommand.Length < 10) continue; // No effectual commands are this small...
                SqlCommand sqlCommand = new SqlCommand(subcommand, sqlConnection.Connection, sqlConnection);
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.ExecuteNonQuery();
            }
        }

        private static string GetScript(string name)
        {
            System.IO.Stream createDbScriptStream = null;
            foreach (string possibleScriptNamespace in new string[] { "RaidLog.Engine.Scripts.{0}.sql", "RaidLog.Engine.DAL.Scripts.{0}.sql" })
            {
                createDbScriptStream = System.Reflection.Assembly.GetExecutingAssembly()
                    .GetManifestResourceStream(String.Format(possibleScriptNamespace, name));
                if (null != createDbScriptStream) break;
            }

            byte[] data = new byte[createDbScriptStream.Length];
            createDbScriptStream.Read(data, 0, data.Length);
            return System.Text.Encoding.ASCII.GetString(data);
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            _SqlConnection.Dispose();
        }

        #endregion
    }
}
